drop proc spa_SEC_ChangeUserPassword
go
/***************************************************************************************************************************  
  
* PROCEDURE: [spa_SEC_ChangeUserPassword]  
  
* PURPOSE: Change password of AS user from front-end website.  
  
* NOTES:   
  
* DATE      VERSION TICKET  AUTHOR                  DESCRIPTION  
  
*---------------------------------------------------------------------------------------------------------------------------  
  
04/14/2011  1.00    16131   Nha Doan				Created.  
  
***************************************************************************************************************************/  
/*
Ex:
spa_SEC_ChangeUserPassword '{21CAFCEA-8629-4FA9-9DC0-85A98AD344D2}','6E-9B-3A-76-20-AA-F7-7F-36-27-75-15-09-77-EE-B8','6E-9B-3A-76-20-AA-F7-7F-36-27-75-15-09-77-EE-00'
*/  
  
CREATE PROCEDURE [dbo].[spa_SEC_ChangeUserPassword]    
 @UserRecId uniqueidentifier,
 @OldPassword varchar(100),    
 @NewPassword varchar(100)    
AS      
--return 1: ok, 2: invalid old password, 3: password was used before
declare @CurrentPassword varchar(100),@OldPwds varchar(1000)
select @CurrentPassword=[UserPassword], @OldPwds = [OldPwds]
from [SEC_Users]
where RecId = @UserRecId

if @CurrentPassword <> @OldPassword
begin
	select 2
	return
end

if @OldPwds is not null and '#'+@OldPwds+'#' like '%#'+@NewPassword+'#%'
begin
	select 3
	return
end

update [SEC_Users]
set   
 [UserPassword] = @NewPassword,    
 [UserPasswordType] = 2,   
 UserPasswordDTS = getdate(),  
 OldPwds = dbo.fcn_calOldPwds(OldPwds,@NewPassword)  
where [RecId] = @UserRecId
select 1
GO